package org.liulinlin.sejw.box.order;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;

import org.liulinlin.sejw.box.*;
import org.liulinlin.sejw.db.*;

import org.osoa.sca.annotations.Destroy;
import org.osoa.sca.annotations.Init;

public class OrderBoxServiceImpl implements OrderBoxService {
	private DBConnectionManager connMgr;
	private OB2USN ob2USN;
	
	@Init
	public void init() {
		connMgr = DBConnectionManager.getInstance();
		ob2USN = OB2USN.getInstance();
	}
	@Destroy
	public void destroy() {
		connMgr.release();
	}
	
	public List<AllocDetails> queryAllocDetails(String commId, String bafId, String senderName) {
		List<AllocDetails> res = new ArrayList<AllocDetails>();
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("OrderBoxService.queryAllocDetails不能获得数据库连接");
			return null;
		}
		
		try {
			Statement stmt = con.createStatement();
			String sql = "select * from AllocDetails";
			boolean where = false;
			String cond = "";
			if (!commId.isEmpty()) {
				if (where) {
					cond = cond + " and " + "Comm_ID = '" + commId + "'";
				} else {
					cond = cond + "Comm_ID = '" + commId + "'";
					where = true;
				}
			}
			if (!bafId.isEmpty()) {
				if (where) {
					cond = cond + " and " + "BAF_ID = '" + bafId + "'";
				} else {
					cond = cond + "BAF_ID = '" + bafId + "'";
					where = true;
				}
			}
			if (where) {
				sql = sql + " where " + cond;
			}
			ResultSet rs = stmt.executeQuery(sql);
		
			while (rs.next()) {
				AllocDetails ad = new AllocDetails(rs.getInt("AD_ID"));
				if (!senderName.isEmpty() && ad.getCommission().getSender().getName() == senderName) {
					res.add(ad);
				}
				if (senderName.isEmpty()) {
					res.add(ad);
				}
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		connMgr.freeConnection("shipping_export", con);
		return res;
	}
	public List<AllocDetails> queryOrderedAllocDetails(String commId, String bafId, String senderName) {
		List<AllocDetails> res = new ArrayList<AllocDetails>();
		List<AllocDetails> temp = queryAllocDetails(commId, bafId, senderName);
		for (int i = 0; i < temp.size(); i++) {
			AllocDetails ad = temp.get(i);
			if (ad.getStatus() == 4) {			//订舱成功的
				res.add(ad);
			}
		}
		return res;
	}
	public String makeContainerLoad(String clId, String snId, String boxId,
			String loadLoc,	String loaderName, String loaderTel, String loaderSign,
			String driverSign, String truckNo) {
		String res = "制单成功";
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("OrderBoxService.makeContainerLoad不能获得数据库连接");
			return null;
		}
		
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("insert into ContainerLoad values('" + clId + "', '" + 
					snId + "', '" + boxId + "', '" + loadLoc + "', '" + loaderName + "', null, '" + loaderTel +
					"', '" + loaderSign + "', NOW(), '" + driverSign + "', '" + truckNo + "', null, null)");
			stmt.executeUpdate("update Box set Box_Status = 2 where Box_ID = '" + boxId + "'");
			stmt.close();
		} catch (SQLException e) {
			res = "SQL异常！";
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		connMgr.freeConnection("shipping_export", con);
		return res;
	}
	public ContainerLoad getContainerLoad(String boxId) {
		ContainerLoad res = null;
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("OrderBoxService.getContainerLoad不能获得数据库连接");
			return null;
		}
		
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from ContainerLoad where Box_ID = '" + boxId + "'");
			rs.next();
			res = new ContainerLoad(rs.getString("CL_ID"));
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		connMgr.freeConnection("shipping_export", con);
		return res;
	}
	public String updateContainerLoad(String clId, String leadSealing) {
		String res = "更新成功";
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("OrderBoxService.updateContainerLoad不能获得数据库连接");
			return null;
		}
		
		try {
			Statement stmt = con.createStatement();
			
			ResultSet rsCL = stmt.executeQuery("select * from ContainerLoad where CL_ID = '" + clId + "'");
			rsCL.next();
			String boxId = rsCL.getString("Box_ID");
			rsCL.close();
			
			stmt.executeUpdate("update Box set Box_LeadSealing ='" + leadSealing + "' where Box_ID = '" + boxId + "'");
			
			stmt.close();
		} catch (SQLException e) {
			res = "SQL异常";
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
	public String makeBox(String boxId) {
		String res = "做箱成功";
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("OrderBoxService.makeBox不能获得数据库连接");
			return null;
		}
		
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("update Box set Box_Made = 1 where Box_ID = '" + boxId + "'");
			
			ResultSet rsBAG = stmt.executeQuery("select * from BoxAndGoods where Box_ID = '" + boxId + "'");
			rsBAG.next();
			String snId = rsBAG.getString("SN_ID");
			rsBAG.close();
			
			ResultSet rsSN = stmt.executeQuery("select * from ShipperNote where SN_ID = '" + snId + "'");
			rsSN.next();
			int adId = rsSN.getInt("AD_ID");
			rsSN.close();
			
			if (isAllBoxMade(adId)) {
				stmt.executeUpdate("update AllocDetails set AD_Status = 6");			//完成做箱
			} else {
				stmt.executeUpdate("update AllocDetails set AD_Status = 5");			//做箱中
			}
			
			stmt.close();
		} catch (SQLException e) {
			res = "SQL异常！";
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
	public void setAdId(String adId) {
		ob2USN.setAdId(adId);
	}
	public void setLoadLoc(String loadLoc) {
		ob2USN.setLoadLoc(loadLoc);
	}
	public String getLoadLoc() {
		return ob2USN.getLoadLoc();
	}
	public void setBagId(int bagId) {
		ob2USN.setBagId(bagId);
	}
	public int getBagId() {
		return ob2USN.getBagId();
	}
	private boolean isAllBoxMade(int adId) {
		boolean res = true;
		
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("OrderBoxService.isAllBoxMade不能获得数据库连接");
			return false;
		}
		
		try {
			Statement stmt = con.createStatement();
			
			ResultSet rsSN = stmt.executeQuery("select * from ShipperNote where AD_ID = '" + adId + "'");
			rsSN.next();
			String snId = rsSN.getString("SN_ID");
			rsSN.close();
			
			ResultSet rsBAG = stmt.executeQuery("select * from BoxAndGoods where SN_ID = '" + snId + "'");
			while (rsBAG.next()) {
				String boxId = rsBAG.getString("Box_ID");
				Statement stmtBox = con.createStatement();
				ResultSet rsBox = stmtBox.executeQuery("select * from Box where Box_ID = '" + boxId + "'");
				rsBox.next();
				if (!rsBox.getBoolean("Box_Made")) {
					res = false;
					break;
				}
				rsBox.close();
			}
			rsBAG.close();
			
			stmt.close();
		} catch (SQLException e) {
			res = false;
			e.printStackTrace();
		} finally {
			//shutdown();
		}
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
}
